package utils;

import jxl.*;
import jxl.read.biff.BiffException;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {
    //beginSheet：指定工作表
    //headerRow：指定工作表标题的行数
    //beginRow：指定需要处理的开始行数
    public static List<Map<String, Object>> importExcel(File excelFile, String[] header, int beginSheet, int headerRow,
                                                        int beginRow) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();
        InputStream in = null;
        Workbook wk = null;
        try {
            in = new FileInputStream(excelFile);
            wk = Workbook.getWorkbook(in);
            //指定工作表
            Sheet sheet = wk.getSheet(beginSheet);
            //获取工作表行数
            int rowCount = sheet.getRows();
            //获取工作表列数
            int columnCount = sheet.getColumns();

            //用于存储数据
            Map<Integer, String> headerIndex = new HashMap<>();

            //循环行
            for (int row = 0; row < rowCount; row++) {
                //存储数据
                Map<String, Object> data = new HashMap<>();
                //循环列
                for (int column = 0; column < columnCount; column++) {
                    //处理标题
                    if (row == headerRow) {
                        //获取标题内容
                        String headerName = sheet.getCell(column, row).getContents();
                        for (String h : header) {
                            //equalsIgnoreCase：字符串与指定对象比较，不考虑大小
                            if (h.equalsIgnoreCase(headerName)) {
                                headerIndex.put(Integer.valueOf(column), h.toLowerCase());
                            }
                        }
                        //需要处理的内容
                    }else if (row >= beginRow) {
                        if (headerIndex.containsKey(Integer.valueOf(column))) {
                            String context = null;

                            Cell cell = sheet.getCell(column, row);
                            try {
                                //如果数据是日期
                                if (cell.getType() == CellType.DATE) {
                                    context = formatDate(((DateCell) cell).getDate());
                                } else if (cell.getType() == CellType.NUMBER
                                        || cell.getType() == CellType.NUMBER_FORMULA) {
                                    context = cell.getContents();
                                } else {
                                    context = cell.getContents();
                                }
                                //错误异常处理
                            } catch (Exception e) {
                                StringBuilder exceptionInfo = new StringBuilder();
                                exceptionInfo.append("行: ").append((row + 1)).append(" ");
                                exceptionInfo.append("列: ").append((column + 1)).append(" ");
                                exceptionInfo.append(headerIndex.get(Integer.valueOf(column))).append(" ");
                                exceptionInfo.append("导入失败!");
                                throw new Exception(exceptionInfo.toString());
                            }
                            data.put(headerIndex.get(Integer.valueOf(column)), context);
                        }
                    }
                }
                //有数据则添加到结果集中
                if (data.size() > 0 && !isAllEmpty(data)) {
                    result.add(data);
                }
            }
        } catch (FileNotFoundException e) {
            System.out.println("Excel文件找不到!");
        } catch (BiffException e) {
            System.out.println("Workbook生成失败!");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (wk != null) {
                wk.close();
            }
            if (in != null) {
                in.close();
            }
        }
        return result;
    }

    //判断数据是否为空
    private static boolean isAllEmpty(Map<String, Object> map) {
        for (String key : map.keySet()) {
            if (!isBlank(map.get(key).toString())) {
                return false;
            }
        }
        return true;
    }

    //判断是否为空
    private static boolean isNull(Object o) {
        return o == null;
    }

    //日期转字符串
    public static String formatDate(Date date) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String result = "";
        if (isNull(date)) {
            return "";
        } else {
            result = dateFormat.format(date);
        }
        return result;
    }

    //判断s是否为空
    private static boolean isBlank(String s) {
        if (isNull(s)) {
            return true;
        }
        return s.trim().length() == 0;
    }
}
